package mqigdcom.mqigd.db;

import android.content.Context;
import android.text.TextUtils;

import mqigdcom.mqigd.CustomApp;
import mqigdcom.mqigd.utils.LogUtils;
import com.google.gson.ExclusionStrategy;
import com.google.gson.FieldAttributes;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.j256.ormlite.android.apptools.OpenHelperManager;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.stmt.Where;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

/**
 * MQI数据库中公路信息的访问接口类
 */
public class MQIDaoImpl implements IMQIDao {
    private static final String TAG = MQIDaoImpl.class.getSimpleName();
    private DBHelper helper;
    private Dao<RoadInfo, Integer> daoRoadInfo;
    private Dao<UnitInfo, Integer> daoUnitInfo;
    private Dao<RoadCondition, Integer> daoRoadCondition;
    private Gson gson;
    private SimpleDateFormat dateFormat;

    public MQIDaoImpl(Context context) {
        helper = OpenHelperManager.getHelper(context, DBHelper.class);
        try {
            daoRoadInfo = helper.getDao(RoadInfo.class);
            daoUnitInfo = helper.getDao(UnitInfo.class);
            daoRoadCondition = helper.getDao(RoadCondition.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
    }

    /**
     * 获取Gson对象
     *
     * @return Gson对象
     */
    private Gson getGson() {
        if (gson == null) {
            gson = new GsonBuilder().setExclusionStrategies(new ExclusionStrategy() {
                @Override
                public boolean shouldSkipField(FieldAttributes f) {
                    String name = f.getName();
                    return "_id".equals(name) || "status".equals(name) || "userid".equals(name) || "start_stake_sort".equals(name);// 忽略字段
                }

                @Override
                public boolean shouldSkipClass(Class<?> clazz) {
                    return false;
                }
            }).create();
        }
        return gson;
    }

    /**
     * 释放各个对象
     */
    @Override
    public void release() {
        OpenHelperManager.releaseHelper();
        daoRoadInfo = null;
        daoUnitInfo = null;
        daoRoadCondition = null;
        helper = null;
    }

    @Override
    public void clearAllRoadList(String userid) {
        if (daoRoadInfo != null && daoUnitInfo != null && !TextUtils.isEmpty(userid)) {
            try {
                // 清除表数据
                daoRoadInfo.executeRaw("delete from " + DBHelper.TABLE_ROAD_INFO + " where userid = \"" + userid + "\"");
                daoUnitInfo.executeRaw("delete from " + DBHelper.TABLE_UNIT_INFO + " where userid = \"" + userid + "\"");
                // 在数据为空的情况下，将自增长的_id归0
                if (daoRoadInfo.queryBuilder().selectColumns("_id").countOf() <= 0) {
                    daoRoadInfo.executeRaw("update sqlite_sequence SET seq = 0 where name = \"" + DBHelper.TABLE_ROAD_INFO + "\"");
                }
                if (daoUnitInfo.queryBuilder().selectColumns("_id").countOf() <= 0) {
                    daoUnitInfo.executeRaw("update sqlite_sequence SET seq = 0 where name = \"" + DBHelper.TABLE_UNIT_INFO + "\"");
                }
                CustomApp.self.sp.edit().remove(userid).apply();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateAllRoadList(String userid, JSONArray roadList) {
        clearAllRoadList(userid);// 首先清除所有的公路信息
        if (daoRoadInfo != null && daoUnitInfo != null && roadList != null && !TextUtils.isEmpty(userid)) {
            List<RoadInfo> roadInfoList = new ArrayList<>();
            List<UnitInfo> unitInfoList = new ArrayList<>();
            try {
                for (int i = 0, len = roadList.length(); i < len; i++) {
                    JSONObject roadObj = roadList.getJSONObject(i);
                    RoadInfo roadInfo = getGson().fromJson(roadObj.toString(), RoadInfo.class);
                    roadInfo.setUserid(userid);
                    roadInfoList.add(roadInfo);
                    JSONArray unitList = roadObj.getJSONArray("unit_list");
                    for (int j = 0, len1 = unitList.length(); j < len1; j++) {
                        UnitInfo unitInfo = getGson().fromJson(unitList.getJSONObject(j).toString(), UnitInfo.class);
                        unitInfo.setStart_stake_sort(Float.parseFloat(unitInfo.getStart_stake()));
                        unitInfo.setUserid(userid);
                        unitInfoList.add(unitInfo);
                    }
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
            try {
                // 批量插入公路信息和单元信息
                daoRoadInfo.create(roadInfoList);
                daoUnitInfo.create(unitInfoList);
                // 保存当前的存储时间
                CustomApp.self.sp.edit().putString(userid, dateFormat.format(new Date())).apply();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public String getAllRoadList(String userid, boolean needAllFields, boolean needUnitList) {
        if (daoRoadInfo != null && daoUnitInfo != null && !TextUtils.isEmpty(userid)) {
            try {
                List<RoadInfo> res;
                if (needAllFields) {
                    res = daoRoadInfo.queryForEq("userid", userid);
                } else {
                    QueryBuilder<RoadInfo, Integer> qb = daoRoadInfo.queryBuilder().selectColumns("road_id", "road_code", "road_name");
                    Where<RoadInfo, Integer> where = qb.where();
                    where.eq("userid", userid);
                    res = qb.query();
                }
                if (!needUnitList) {
                    return getGson().toJson(res);
                }
                List<RoadInfo> list = new ArrayList<>();
                for (int i = 0, len = res.size(); i < len; i++) {
                    RoadInfo roadInfo = res.get(i);
                    QueryBuilder<UnitInfo, Integer> qb = daoUnitInfo.queryBuilder();
                    Where<UnitInfo, Integer> where = qb.where();
                    where.eq("road_id", roadInfo.getRoad_id()).and().eq("userid", userid);
                    List<UnitInfo> result;
                    if (needAllFields) {
                        result = qb.query();
                        roadInfo.setUnit_list(result);
                        list.add(roadInfo);
                    } else {
                        // 需去除重复的单元路段
                        qb.selectColumns("unit_id", "start_stake", "end_stake", "pavement_type", "line_dir", "column");
                        qb.orderBy("start_stake_sort", true);
                        result = qb.query();
                        if (result != null && result.size() > 0) {
                            List<UnitInfo> list1 = new ArrayList<>();
                            for (int j = 0, count = result.size(); j < count; j++) {
                                // 将Item中的line_dir和unit_id转为数组返回
                                if (list1.size() <= 0) {
                                    list1.add(result.get(j));
                                    continue;
                                }
                                UnitInfo info = result.get(j);
                                boolean exist = false;// 该单元信息是否已经存在
                                for (int k = 0, count1 = list1.size(); k < count1; k++) {
                                    UnitInfo info1 = list1.get(k);
                                    // 检测同一路段，是否有不同的调查方向
                                    if (info.getStart_stake().equals(info1.getStart_stake())
                                            && info.getEnd_stake().equals(info1.getEnd_stake())) {
                                        String dir1 = info1.getLine_dir();
                                        String dir = info.getLine_dir();
                                        if (!dir1.contains(dir)) {
                                            info1.setLine_dir(dir1 + "," + dir);
                                            info1.setUnit_id(info1.getUnit_id() + "," + info.getUnit_id());
                                            list1.set(k, info1);
                                        }
                                        exist = true;
                                        break;
                                    }
                                }
                                if (!exist) {
                                    list1.add(info);
                                }
                            }
                            roadInfo.setUnit_list(list1);
                            list.add(roadInfo);
                        }
                    }
                }
                return getGson().toJson(list);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return "[]";
    }

    @Override
    public boolean isEmpty(String userid) {
        if (daoRoadInfo != null && !TextUtils.isEmpty(userid)) {
            try {
                QueryBuilder<RoadInfo, Integer> qb = daoRoadInfo.queryBuilder().selectColumns("_id");
                Where<RoadInfo, Integer> where = qb.where();
                where.eq("userid", userid);
                return where.countOf() <= 0;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public void updateOrCreateRoadCondition(RoadCondition roadCondition) {
        if (daoRoadCondition != null && roadCondition != null) {
            try {
                QueryBuilder<RoadCondition, Integer> qb = daoRoadCondition.queryBuilder().selectColumns("_id", "value");
                Where<RoadCondition, Integer> where = qb.where();
                where.eq("userid", roadCondition.getUserid())
                        .and().eq("road_id", roadCondition.getRoad_id())
                        .and().eq("unit_id", roadCondition.getUnit_id())
                        .and().eq("x", roadCondition.getX())
                        .and().eq("y", roadCondition.getY())
                        .and().eq("line_dir", roadCondition.getLine_dir())
                        .and().eq("type", roadCondition.getType());
                RoadCondition item = qb.queryForFirst();
                if (item != null) {
                    if (!item.getValue().equals(roadCondition.getValue())) {// 修改的不是同一值
                        int result;
                        switch (item.getStatus()) {// 0 未上传；1 正在上传
                            case 0:
                                roadCondition.set_id(item.get_id());
                                result = daoRoadCondition.update(roadCondition);
                                LogUtils.d(TAG, "update (status == 0) result = " + result);
                                break;
                            case 1:
                                result = daoRoadCondition.create(roadCondition);
                                LogUtils.d(TAG, "create (status == 1) result = " + result);
                                break;
                        }
                    }
                } else {
                    int result = daoRoadCondition.create(roadCondition);// 插入新数据
                    LogUtils.d(TAG, "create result = " + result);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<RoadCondition> getNeedUploadRoadConditions() {
        if (daoRoadCondition != null) {
            try {
                List<RoadCondition> list = daoRoadCondition.queryForEq("status", 0);// 所有未上传的数据
                if (list == null || list.size() <= 0) {
                    try {
                        // 在数据为空的情况下，将自增长的_id归0
                        if (daoRoadCondition.queryBuilder().selectColumns("_id").countOf() <= 0) {
                            daoRoadCondition.executeRaw("update sqlite_sequence SET seq = 0 where name = \"" + DBHelper.TABLE_ROAD_CONDITION + "\"");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                return list;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public String getRoadConditionsById(String userid, String road_id, String unit_id, String
            line_dir, String type) {
        if (daoRoadCondition != null && !TextUtils.isEmpty(road_id) && !TextUtils.isEmpty(unit_id)
                && !TextUtils.isEmpty(line_dir) && !TextUtils.isEmpty(type)) {
            QueryBuilder<RoadCondition, Integer> qb = daoRoadCondition.queryBuilder()
                    .selectColumns("x", "y", "value");
            Where<RoadCondition, Integer> where = qb.where();
            try {
                where.eq("userid", userid)
                        .and().eq("road_id", road_id)
                        .and().eq("unit_id", unit_id)
                        .and().eq("line_dir", line_dir)
                        .and().eq("type", type);
                List<RoadCondition> list = qb.query();
                if (list != null) {
                    return getGson().toJson(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return "[]";
    }

    @Override
    public void updateRoadConditionUploadStatus(int _id, int status) {
        if (daoRoadCondition != null) {
            UpdateBuilder<RoadCondition, Integer> ub = daoRoadCondition.updateBuilder();
            Where<RoadCondition, Integer> where = ub.where();
            try {
                where.eq("_id", _id);
                int result = ub.updateColumnValue("status", status).update();
                LogUtils.d(TAG, "updateRoadConditionUploadStatus() _id = " + _id + ", result = " + result);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteRoadConditionById(int _id) {
        if (daoRoadCondition != null) {
            try {
                int result = daoRoadCondition.deleteById(_id);
                LogUtils.d(TAG, "deleteRoadConditionById() _id = " + _id + ", result = " + result);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}